package zunge.lbe1;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;

import zunge.tools.DbConn;
import zunge.tools.Tools;

/**
 * [1] Generate input files from SQL database
 * 
 * Input: 
 * 		lbe_stream.stat_uap
 * 		lbe_stream.af
 * Process:
 * 		Remove users who: 
 * 			(1) Never specify any permission decision 
 * 			(2) Always allow/reject/prompt (more than 95%)
 * 		Remove apps which:
 * 			(1) Has less than 20 users in the db
 * 			(2) Never requested any permission
 * 		Remove "default" decisions
 * Output:
 * 		uMap, aMap		user_id uId		app_id aId
 * 		record_u		uId[ aId:pId:d]
 * 		record_uap		uId aId pId d
 * 		feature_a		aId features...
 */
public class Data {

	public String folder;

	public String getScriptDir(){return folder+"script/";}
	public String getResultDir(){return folder+"result/";}
	public String getTempDir(){return folder+"temp/";}

	public String getUMapFile(){return folder+"uMap.txt";}
	public String getAMapFile(){return folder+"aMap.txt";}
	public String getTopASetFile(){return folder + "topASet.txt";}
	public String getRecordUFile(){return folder+"record_u.txt";}
	public String getRecordUAPFile(){return folder+"record_uap.txt";}
	//public String getFeatureAFile(){return folder+"feature_a.txt";}
	
	public HashMap<Long,Integer> uMap;//user_id -> uId
	public HashMap<Long,Integer> aMap;//app_id -> aId
	public HashSet<Integer> topASet;//top 100 apps (aId)
	
	public static final int P = 12; //#permissions
	public static final int TOP_A = 100; //Top list include top 100 apps (by #users)
	
	public static final int CORES = 12;//#cores used for computation
	
	
	public Data(String folder, boolean create, DbConn conn) throws SQLException, IOException{
		this.folder = folder;
		
		if(create){
			new File(folder).mkdir();
			new File(getScriptDir()).mkdir();
			new File(getResultDir()).mkdir();
			new File(getTempDir()).mkdir();
			
			/* lbe_stream.stat_uap
				+----------------+------------+------+-----+---------+----------------+
				| Field          | Type       | Null | Key | Default | Extra          |
				+----------------+------------+------+-----+---------+----------------+
				| id             | int(11)    | NO   | PRI | NULL    | auto_increment |
				| u_id           | bigint(20) | YES  | MUL | NULL    |                |
				| a_id           | bigint(20) | YES  |     | NULL    |                |
				| p_id           | bigint(20) | YES  |     | NULL    |                |
				| d_final        | int(11)    | YES  |     | NULL    |                |
				| d_previous     | int(11)    | YES  |     | NULL    |                |
				| d_backandforth | tinyint(1) | YES  |     | NULL    |                |
				+----------------+------------+------+-----+---------+----------------+
				lbe_stream.af
				+----------------+------------+------+-----+---------+-------+
				| Field          | Type       | Null | Key | Default | Extra |
				+----------------+------------+------+-----+---------+-------+
				| a_id           | bigint(20) | NO   | PRI | NULL    |       |
				| wl             | tinyint(1) | YES  |     | NULL    |       |
				| u_count        | int(11)    | YES  |     | NULL    |       |
				| category       | int(11)    | YES  |     | NULL    |       |
				| content_rating | int(11)    | YES  |     | NULL    |       |
				| rating_value   | double     | YES  |     | NULL    |       |
				| rating_5star   | double     | YES  |     | NULL    |       |
				| rating_4star   | double     | YES  |     | NULL    |       |
				| rating_3star   | double     | YES  |     | NULL    |       |
				| rating_2star   | double     | YES  |     | NULL    |       |
				| rating_1star   | double     | YES  |     | NULL    |       |
				| price          | double     | YES  |     | NULL    |       |
				+----------------+------------+------+-----+---------+-------+
			 */
			
			//Remove users who have uniform decisions (99% or more) on all APs.
			HashSet<Long> validUsers = new HashSet<Long>();
			String query = "select u_id, count(*) total, "
					+" sum(case when d_final = 1 then 1 else 0 end) acc,"
					+" sum(case when d_final = 2 then 1 else 0 end) rej,"
					+" sum(case when d_final = 3 then 1 else 0 end) pmp"
					+" from stat_uap group by u_id";
			ResultSet rs = conn.executeQuery(query);
			while(rs.next()){
				double total = rs.getDouble(2);
				for(int i = 3;i < 6;++i){
					if(rs.getDouble(i)/total > 0.99){continue;}
				}
				validUsers.add(rs.getLong(1));
			}
			/**/System.out.println("\t#users="+validUsers.size());
			rs.close();
			
			//Remove apps which have less than 10 users in the database, Collect Top Apps
			HashSet<Long> validApps = new HashSet<Long>();
			List<AU> aus = new ArrayList<AU>();
			query = "select au.a_id , au.ucount from " +
					"(select a_id, count(distinct u_id) ucount from stat_uap group by a_id) au " +
					"join af on au.a_id = af.a_id where au.ucount >= 10";
			rs = conn.executeQuery(query);
			while(rs.next()){
				validApps.add(rs.getLong(1));
				aus.add(new AU(rs.getLong(1),rs.getInt(2)));
			}
			/**/System.out.println("\t#apps="+validApps.size());
			rs.close();
			
			//Generate uMap,aMap
			List<Long> uList = new ArrayList<Long>(validUsers);
			List<Long> aList = new ArrayList<Long>(validApps);
			Collections.shuffle(uList);
			Collections.shuffle(aList);
			uMap = new HashMap<Long,Integer>();
			aMap = new HashMap<Long,Integer>();
			for(int i = 0;i < uList.size();++i){uMap.put(uList.get(i), i+1);}
			for(int i = 0;i < aList.size();++i){aMap.put(aList.get(i), i+1);}
			Tools.saveMap(uMap, getUMapFile());
			Tools.saveMap(aMap, getAMapFile());
			
			//Generate top app set
			Collections.sort(aus);
			/**/
			topASet = new HashSet<Integer>(Data.TOP_A);
			for(int i = 0;i < aus.size() && i < Data.TOP_A;++i){
				//System.out.println(aus.get(i).a_id+" "+aus.get(i).ucount);
				topASet.add(aMap.get(aus.get(i).a_id));
			}
			Tools.saveSet(topASet, getTopASetFile());
			
			
			//Generate record_u, record_uap
			BufferedWriter bwu = new BufferedWriter(new FileWriter(getRecordUFile()));
			BufferedWriter bwuap = new BufferedWriter(new FileWriter(getRecordUAPFile()));
			query = "SET SESSION group_concat_max_len = 10000000;";
			conn.executeQuery(query);
			query = "select u_id, group_concat(a_id,':',p_id,':',d_final separator ' ') from stat_uap group by u_id";
			rs = conn.executeQueryInStream(query);
			
			long u_id,a_id;int p_id,d_id;
			int uid, aid, pid, did;
			while(rs.next()){
				u_id = rs.getLong(1);
				if(!uMap.containsKey(u_id)){continue;}
				uid = uMap.get(u_id);
				
				String[] apds = rs.getString(2).split(" ");
				StringBuffer sb = new StringBuffer();
				sb.append(uid);
				for(String apd:apds){
					String[] frags = apd.split(":");
					a_id = Long.parseLong(frags[0]);
					if(!aMap.containsKey(a_id)){continue;}
					aid = aMap.get(a_id);
					
					p_id = Integer.parseInt(frags[1]);
					pid = p_id + 1;
					
					d_id = Integer.parseInt(frags[2]);
					switch(d_id){
					case 1:did = 1;break;
					case 2:did = -1;break;
					case 3:did = 0;break;
					default:continue;}
					bwuap.write(uid+" "+aid+" "+pid+" "+did+"\n");
					sb.append(" "+aid+":"+pid+":"+did);
				}
				bwu.write(sb.toString()+"\n");
			}
			bwu.close();
			bwuap.close();
			rs.close();
			
			//TO-DO Generate feature_a
			
		}else{
			//Load uMap,aMap,topASet
			uMap = Tools.loadMap(getUMapFile());
			aMap = Tools.loadMap(getAMapFile());
			topASet = Tools.loadSet(getTopASetFile());
		}
	}
	
	static final class AU implements Comparable<AU>{
		Long a_id;
		Integer ucount;
		public AU(long a_id, int ucount){this.a_id = a_id; this.ucount = ucount;}
		@Override
		public int compareTo(AU arg0) {
			return arg0.ucount.compareTo(this.ucount);
		}
	}
	
	public String[] getALabels(DbConn conn) throws SQLException{
		String query = "select a_id, a_name from a";
		ResultSet rs = conn.executeQueryInStream(query);
		String[] alabels = new String[aMap.size()];
		long a_id;int aid;
		while(rs.next()){
			a_id = rs.getLong(1);
			if(aMap.containsKey(a_id)){
				aid = aMap.get(a_id);
				alabels[aid-1] = rs.getString(2);
			}
		}
		rs.close();
		return alabels;
	}
	public String[] getPLabels(DbConn conn) throws SQLException{
		String query = "select p_id, p_name from p";
		ResultSet rs = conn.executeQueryInStream(query);
		String[] plabels = new String[P];
		int p_id;
		while(rs.next()){
			p_id = rs.getInt(1);
			plabels[p_id] = rs.getString(2);
		}
		rs.close();
		return plabels;
	}
}
